﻿using MainWebService;
using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.IO;
using System.Linq;
using System.Text;
using System.Web;

namespace MainWebService
{
    public class UserOperations
    {
        private UserDetails GetUserDetail(string userName, string password, string deviceId, string deviceName)
        {
            object msg = null;
            UserDetails usDetails = new UserDetails();
            DataSet ds = new DataSet();
            DataTable dtServiceDetails = new DataTable();
            dtServiceDetails.Columns.Add(new DataColumn("ItemId"));
            dtServiceDetails.Columns.Add(new DataColumn("ServiceName"));
            DataRow dr = null;
            DAO d = new DAO();
            List<SqlParameter> param = new List<SqlParameter>();
            param.Add(d.AddParameter("@UserName", userName));
            param.Add(d.AddParameter("@Password", password));
            param.Add(d.AddParameter("@DeviceId", deviceId));
            param.Add(d.AddParameter("@DeviceName", deviceName));
            param.Add(d.AddParameter("@Message", msg, SqlDbType.VarChar, 500, ParameterDirection.InputOutput));
            ds = d.ExecuteDataSet("P_ValidateUser", param, ref msg);

            if (msg != null && !string.IsNullOrWhiteSpace(msg.ToString()))
            {
                return new UserDetails() { LoginMessage = msg.ToString(), IsUserValidated = false };
            }
            if (ds.Tables.Count > 0)
            {
                if (Utility.IsValidDataSet(ds, 3) && !String.IsNullOrWhiteSpace(Utility.GetString(ds.Tables[3].Rows[0][0])))
                {
                    usDetails.IsUserValidated = false;
                    usDetails.LoginMessage = "User is not activate. Please activate then login";
                }
                else
                {
                    if (ds.Tables[0] != null && ds.Tables[0].Rows.Count > 0)
                    {
                        usDetails.UserName = userName;
                        usDetails.UserId = Convert.ToInt32(ds.Tables[0].Rows[0]["UserId"]);
                        usDetails.ClientId = Convert.ToInt32(ds.Tables[0].Rows[0]["ClientId"]);
                        usDetails.DeviceLimit = Convert.ToInt32(ds.Tables[0].Rows[0]["DeviceLimit"]);
                        usDetails.Company = Convert.ToString(ds.Tables[0].Rows[0]["Company"]);
                        usDetails.EmailId = Convert.ToString(ds.Tables[0].Rows[0]["EmailAddress"]);
                        usDetails.RoleId = Utility.GetInt(ds.Tables[0].Rows[0]["UserRole"]);
                        usDetails.AccountExpiry = Utility.GetDateTime(ds.Tables[0].Rows[0]["ExpiryDate"]).Value;
                        usDetails.TimeZone = Convert.ToString(ds.Tables[0].Rows[0]["TimeZone"]);
                        usDetails.Password = Convert.ToString(ds.Tables[0].Rows[0]["Password"]);
                        usDetails.FirstName = Convert.ToString(ds.Tables[0].Rows[0]["FirstName"]);
                        usDetails.LastName = Convert.ToString(ds.Tables[0].Rows[0]["LastName"]);
                        usDetails.PhoneNumber = Convert.ToString(ds.Tables[0].Rows[0]["PhoneNumber"]);
                    }
                    if (ds.Tables[1] != null && ds.Tables[1].Rows.Count > 0)
                    {

                        usDetails.HashCode = Convert.ToString(ds.Tables[1].Rows[0]["HashCode"]);
                        usDetails.LastLogin = Convert.ToString(ds.Tables[1].Rows[0]["LoggedInTime"]);
                    }
                    if (ds.Tables[2] != null && ds.Tables[2].Rows.Count > 0)
                    {
                        usDetails.Services = new List<ServiceDetails>();
                        Dictionary<int, string> lstService = new Dictionary<int, string>();
                        for (int i = 0; i < ds.Tables[2].Rows.Count; i++)
                        {
                            dr = dtServiceDetails.NewRow();
                            dr[0] = Convert.ToInt32(ds.Tables[2].Rows[i]["ItemId"]);
                            dr[1] = Convert.ToString(ds.Tables[2].Rows[i]["ServiceName"]);
                            dtServiceDetails.Rows.Add(dr);
                            usDetails.Services.Add(new ServiceDetails(Convert.ToInt32(ds.Tables[2].Rows[i]["ItemId"]), usDetails.ClientId, usDetails.HashCode));

                        }
                        usDetails.GetServiceList = new DataSet();
                        usDetails.GetServiceList.Tables.Add(dtServiceDetails);
                    }

                    usDetails.IsUserValidated = true;
                    if (usDetails.RoleId == 2)
                        usDetails.ClientId = usDetails.UserId;
                    usDetails.LoginMessage = "User Validated Successfully";
                }
            }
            else
            {
                usDetails.IsUserValidated = false;
                usDetails.LoginMessage = "Invalid Login Details. Please try again";
            }
            return usDetails;
        }

        internal string AddUser(string emailId, string appUserName, string password, string company)
        {
            /* object messgae = null;
             DAO d = new DAO();
              List<SqlParameter> param = new List<SqlParameter>();
              param.Add(d.AddParameter("@EmailId", emailId));
              param.Add(d.AddParameter("@UserName", appUserName));
              param.Add(d.AddParameter("@Password", password));
              param.Add(d.AddParameter("@Company", company));
              param.Add(d.AddParameter("@Messages", messgae,SqlDbType.VarChar,500,ParameterDirection.InputOutput));
              d.ExecuteQuery("P_AddUser", param, ref messgae);
         
              return messgae.ToString();
             *   * */
            return "Method not implemented";
        }

        internal UserDetails ValidateUser(string UserName, string _password, string deviceId, string deviceName)
        {
            return GetUserDetail(UserName, _password, deviceId, deviceName);
        }

        internal string AddUser(int userId, string EmailId, string UserName, string _password, string Company, int DeviceLimit, int ClientId, List<string> ServiceList, string hashcode)
        {
            object messgae = null;
            DAO d = new DAO();

            List<SqlParameter> param = new List<SqlParameter>();
            param.Add(d.AddParameter("@UserId", userId));
            param.Add(d.AddParameter("@HashCode", hashcode));
            param.Add(d.AddParameter("@EmailId", EmailId));
            param.Add(d.AddParameter("@UserName", UserName));
            param.Add(d.AddParameter("@Password", _password));
            param.Add(d.AddParameter("@Company", Company));
            param.Add(d.AddParameter("@DeviceCount", DeviceLimit));
            if (ServiceList != null && ServiceList.Count > 0)
            {
                string serviceListcsv = string.Join(",", ServiceList.Select(x => x).ToArray());
                param.Add(d.AddParameter("@ServiceList", serviceListcsv));
            }
            if (ClientId > 0)
            {
                param.Add(d.AddParameter("@ClientId", ClientId));
            }
            param.Add(d.AddParameter("@Messages", messgae, SqlDbType.VarChar, 500, ParameterDirection.InputOutput));
            try
            {
                d.ExecuteQuery("P_AddUser", param, ref messgae);

                //Send notification to administrator
                //SendNotification(EmailId, UserName, Company);
                // Send activation mail to user
                SendActivationMail(EmailId, messgae.ToString().Split(',')[0], messgae.ToString().Split(',')[1], UserName);
                messgae = "0";
            }
            catch
            {

            }
            return messgae.ToString();
        }

        private static void SendNotification(UserDetails objUser)
        {
            TimeZoneInfo timeZoneInfo = TimeZoneInfo.FindSystemTimeZoneById("Pacific Standard Time");
            DateTime newDateTime = TimeZoneInfo.ConvertTime(DateTime.Now, timeZoneInfo);
            string _type = "";
            if (!string.IsNullOrWhiteSpace(objUser.EmailId))
                _type = "Administrator";
            else
                _type = "User";
            Emailer _mail = new Emailer();
            _mail.To = "support@ssica.org";
            _mail.Subject = "New " + _type + " registered on ssica.org";
            _mail.MailBody = @"<b>New " + _type + " with following detail has been registered on ssica.org</b><br/><br/>" +
            "<table width='600px' border='1' cellspacing='2' cellpadding='2'>" +
            (!string.IsNullOrWhiteSpace(objUser.EmailId) ? "<tr><td>Email Id :</td><td>" + objUser.EmailId + "</td></tr>" : "") +
            "<tr><td>UserName :</td><td>" + objUser.UserName + "</td></tr>" +
            (!string.IsNullOrWhiteSpace(objUser.FirstName) ? "<tr><td>First Name :</td><td>" + objUser.FirstName + "</td></tr>" : "") +
            (!string.IsNullOrWhiteSpace(objUser.LastName) ? "<tr><td>Last Name :</td><td>" + objUser.LastName + "</td></tr>" : "") +
            (!string.IsNullOrWhiteSpace(objUser.PhoneNumber) ? "<tr><td>Phone Number :</td><td>" + objUser.PhoneNumber + "</td></tr>" : "") +
            "<tr><td>Company :</td><td>" + objUser.Company + "</td></tr><tr>" +
            "<td>Registration Date :</td><td>" + newDateTime + "</td></tr>" +
            "</table>";
            _mail.SendMail();
        }

        private static void SendActivationMail(string EmailId, string hashCode, string userId, string UserName)
        {
            if (!string.IsNullOrWhiteSpace(EmailId))
            {
                Emailer _mail1 = new Emailer();
                _mail1.To = EmailId;
                _mail1.Subject = "Account activation on ssica.org for the user " + UserName;
                _mail1.BCC = "support@ssica.org";
                _mail1.MailBody = @"<b>You have registred your account on ssica.org recently. You need to verify your email id in order to activate your account.</br></b>
                                    <b>Please click <a href='http://ssica.org/Activation.aspx?hash=" + hashCode + "&userid=" + userId + "'>here</a> to activate your account or copy paste the below url in your browser<br/></b>" +
                                    "http://ssica.org/Activation.aspx?hash=" + hashCode + "&userid=" + userId + " <p>&nbsp;</p> <br/><br/> Regards,<br/> Admin<br/> SSICA.ORG";
                _mail1.SendMail();
            }
        }

        private static void SendPasswordResetMail(string EmailId, string hashCode)
        {
            Emailer _mail1 = new Emailer();
            _mail1.To = EmailId;
            _mail1.Subject = "SSICA - Reset Password";
            _mail1.MailBody = @"Click the link below to reset your password:</br>
                              <a href='http://ssica.org/ResetPassword.aspx?key=" + hashCode + "'>http://ssica.org/ResetPassword.aspx?key=" + hashCode + "</a><br/>" +
                               "If clicking the link above doesn't work, please copy and paste the URL in a new browser window instead.<br/>" +
                   "<p><br/> This is an automated message. Please do not reply to this email. If you need help,please contact us at support@ssica.org .</p>" +
                   "<br/><br/> Thanks," +
                    "<br/>The SSICA Team";
            _mail1.SendMail();
        }
        internal string DeleteUser(int UserId, int ClientId, string hashCode)
        {
            object messgae = null;
            DAO d = new DAO();

            List<SqlParameter> param = new List<SqlParameter>();
            param.Add(d.AddParameter("@UserId", UserId));
            param.Add(d.AddParameter("@ClientId", ClientId));
            param.Add(d.AddParameter("@HashCode", hashCode));
            param.Add(d.AddParameter("@Messages", messgae, SqlDbType.VarChar, 500, ParameterDirection.InputOutput));
            d.ExecuteQuery("P_DeleteUser", param, ref messgae);
            return messgae.ToString();
        }

        internal string UpdateUser(int UserId, string UserName, string _password, int DeviceLimit, int ClientId, List<string> ServiceList, string HashCode)
        {
            object messgae = null;
            DAO d = new DAO();

            List<SqlParameter> param = new List<SqlParameter>();
            param.Add(d.AddParameter("@UserId", UserId));
            param.Add(d.AddParameter("@UserName", UserName));
            param.Add(d.AddParameter("@Password", _password));
            param.Add(d.AddParameter("@HashCode", HashCode));
            param.Add(d.AddParameter("@DeviceCount", DeviceLimit));
            if (ServiceList != null && ServiceList.Count > 0)
            {
                string serviceListcsv = string.Join(",", ServiceList.Select(x => x).ToArray());
                param.Add(d.AddParameter("@ServiceList", serviceListcsv));
            }
            if (ClientId > 0)
            {
                param.Add(d.AddParameter("@ClientId", ClientId));
            }
            param.Add(d.AddParameter("@Messages", messgae, SqlDbType.VarChar, 500, ParameterDirection.InputOutput));
            d.ExecuteQuery("P_UpdateUser", param, ref messgae);
            return messgae.ToString();
        }

        internal List<UserDetails> ListUsers(int clientId, string HashCode)
        {
            List<UserDetails> lstUsers = new List<UserDetails>();
            UserDetails usDetails = null;
            DataSet ds = new DataSet();
            DataTable dtService = null;
            DataRow dr = null;
            DAO d = new DAO();
            int itemId = 0;
            List<SqlParameter> param = new List<SqlParameter>();
            param.Add(d.AddParameter("@ClientId", clientId));
            param.Add(d.AddParameter("@HashCode", HashCode));
            ds = d.ExecuteDataSet("P_ListUsers", param);
            if (ds.Tables.Count > 0)
            {
                if (ds.Tables[0] != null && ds.Tables[0].Rows.Count > 0)
                {
                    for (int i = 0; i < ds.Tables[0].Rows.Count; i++)
                    {
                        usDetails = new UserDetails();
                        usDetails.UserName = Convert.ToString(ds.Tables[0].Rows[i]["UserName"]);
                        usDetails.UserId = Convert.ToInt32(ds.Tables[0].Rows[i]["UserId"]);
                        usDetails.ClientId = Convert.ToInt32(ds.Tables[0].Rows[i]["ClientId"]);
                        usDetails.DeviceLimit = Convert.ToInt32(ds.Tables[0].Rows[i]["DeviceLimit"]);
                        usDetails.LastLogin = Convert.ToString(ds.Tables[0].Rows[i]["LoggedInTime"]);
                        usDetails.CreateDate = Convert.ToString(ds.Tables[0].Rows[i]["CreateDate"]);
                        usDetails.Password = Utility.GetString(ds.Tables[0].Rows[i]["Password"]);
                        string[] itemIds = Convert.ToString(ds.Tables[0].Rows[i]["ItemIds"]).Split(',');
                        string[] serviceNames = Convert.ToString(ds.Tables[0].Rows[i]["ServiceName"]).Split(',');
                        itemIds = new HashSet<string>(itemIds).ToArray<string>(); // removing duplicates if any
                        serviceNames = new HashSet<string>(serviceNames).ToArray<string>();
                        Dictionary<int, string> lstService = new Dictionary<int, string>();
                        dtService = new DataTable();
                        dtService.Columns.Add(new DataColumn("Id"));
                        dtService.Columns.Add(new DataColumn("Name"));
                        for (int j = 0; j < itemIds.Length; j++)
                        {

                            if (Int32.TryParse(itemIds[j], out itemId) && itemId > 0)
                            {
                                dr = dtService.NewRow();
                                dr[0] = itemId;
                                dr[1] = serviceNames[j] != null ? Convert.ToString(serviceNames[j]) : "";
                                dtService.Rows.Add(dr);
                            }
                        }

                        usDetails.GetServiceList = new DataSet();
                        usDetails.GetServiceList.Tables.Add(dtService);
                        lstUsers.Add(usDetails);
                    }
                }
                else
                {
                    usDetails = new UserDetails();
                    usDetails.LoginMessage = "No Data Found";
                    lstUsers.Add(usDetails);
                }
            }
            else
            {
                usDetails = new UserDetails();
                usDetails.LoginMessage = "No Row Found";
                lstUsers.Add(usDetails);
            }


            return lstUsers;
        }

        internal UserDetails ListUser(int userId, int clientId, string HashCode)
        {
            UserDetails usDetails = new UserDetails();
            DataSet ds = new DataSet();
            DataTable dtService = new DataTable();
            DataRow dr = null;
            DAO d = new DAO();
            int itemId = 0;
            List<SqlParameter> param = new List<SqlParameter>();
            param.Add(d.AddParameter("@UserId", userId));
            param.Add(d.AddParameter("@ClientId", clientId));
            param.Add(d.AddParameter("@HashCode", HashCode));
            ds = d.ExecuteDataSet("P_GetAppUser", param);
            if (ds.Tables.Count > 0)
            {
                if (ds.Tables[0] != null && ds.Tables[0].Rows.Count > 0)
                {
                    usDetails.UserName = Convert.ToString(ds.Tables[0].Rows[0]["UserName"]);
                    usDetails.Password = Convert.ToString(ds.Tables[0].Rows[0]["Password"]);
                    usDetails.UserId = Convert.ToInt32(ds.Tables[0].Rows[0]["UserId"]);
                    usDetails.ClientId = Convert.ToInt32(ds.Tables[0].Rows[0]["ClientId"]);
                    usDetails.DeviceLimit = Convert.ToInt32(ds.Tables[0].Rows[0]["DeviceLimit"]);
                    usDetails.CreateDate = Convert.ToString(ds.Tables[0].Rows[0]["CreateDate"]);
                    string[] itemIds = Convert.ToString(ds.Tables[0].Rows[0]["ItemIds"]).Split(',');
                    // string[] serviceNames = Convert.ToString(ds.Tables[0].Rows[0]["ServiceName"]).Split(',');
                    dtService.Columns.Add(new DataColumn("Id"));
                    dtService.Columns.Add(new DataColumn("Name"));
                    for (int j = 0; j < itemIds.Length; j++)
                    {
                        if (Int32.TryParse(itemIds[j], out itemId) && itemId > 0)
                        {
                            dr = dtService.NewRow();
                            dr[0] = itemId;
                            dr[1] = "";
                            dtService.Rows.Add(dr);
                            //lstService.Add(itemId, "");
                        }
                    }
                    // usDetails.GetServiceList = lstService;
                    usDetails.GetServiceList = new DataSet();
                    usDetails.GetServiceList.Tables.Add(dtService);
                    usDetails.LoginMessage = "0";
                }
                else
                {
                    usDetails.LoginMessage = "No Data Found";
                }
            }
            else
            {
                usDetails.LoginMessage = "No Row Found";
            }


            return usDetails;
        }

        internal DataSet GetUsers(int userId, string hashCode)
        {
            try
            {
                DAO d = new DAO();
                string query = "SELECT UserId,AppUserName FROM UserDetails Where ClientId={0}";
                return d.ExecuteDataSet(string.Format(query, userId));
            }
            catch (Exception ex)
            {
                return new DataSet();
            }
        }

        internal void AssociateService(int UserId, string HashCode, int ServiceId, string usersList)
        {
            DAO d = new DAO();

            List<SqlParameter> param = new List<SqlParameter>();
            param.Add(d.AddParameter("@CreatedBy", UserId));
            param.Add(d.AddParameter("@ServiceId", ServiceId));
            param.Add(d.AddParameter("@UsersList", usersList));
            param.Add(d.AddParameter("@HashCode", HashCode));
            d.ExecuteQuery("P_AssociateUserToService", param);
        }

        internal DataSet GetServicesList(int clientId, string hashCode)
        {
            //Dictionary<string, string> _result = null;
            DAO d = new DAO();
            List<SqlParameter> param = new List<SqlParameter>();
            param.Add(d.AddParameter("@ClientId", clientId));
            param.Add(d.AddParameter("@HashCode", hashCode));
            DataSet ds = d.ExecuteDataSet("P_GetServicesList", param);
            return ds;
        }
        internal DataSet GetDevicesList(int clientId, string hashCode)
        {
            DAO d = new DAO();
            List<SqlParameter> param = new List<SqlParameter>();
            param.Add(d.AddParameter("@ClientId", clientId));
            param.Add(d.AddParameter("@HashCode", hashCode));
            DataSet ds = d.ExecuteDataSet("P_GetDevicesList", param);
            return ds;
        }

        internal DataSet GetUsersList(int clientId, string hashCode)
        {
            DAO d = new DAO();
            List<SqlParameter> param = new List<SqlParameter>();
            param.Add(d.AddParameter("@ClientId", clientId));
            param.Add(d.AddParameter("@HashCode", hashCode));
            DataSet ds = d.ExecuteDataSet("P_GetUsersList", param);
            return ds;
        }

        internal string ActivateUser(int userId, string hash)
        {
            DAO d = new DAO();
            try
            {
                string query = "DELETE FROM UserActivation WHERE HashCode='{0}' AND UserId={1}";
                int result = d.ExecuteQuery(string.Format(query, hash, userId));
                if (result > 0)
                    return "Account Activated Successfully";
                else
                    return "Either link is expired or Account is already activated";
            }
            catch (Exception)
            {
                return "Error while activating account. Please contact system administrator of SSICA.ORG";
            }
        }

        internal string GetSecurity(string email, string type)
        {
            try
            {
                if (type == "AM")
                {
                    object msg = "";
                    DAO d = new DAO();
                    List<SqlParameter> param = new List<SqlParameter>();
                    param.Add(d.AddParameter("@EmailId", email));
                    param.Add(d.AddParameter("@Message", msg.ToString(), SqlDbType.VarChar, 100, ParameterDirection.Output));
                    DataSet ds = d.ExecuteDataSet("P_GetActivationMail", param, ref msg);
                    if (msg != null && msg.ToString().Trim().Equals("0"))
                    {
                        if (Utility.IsValidDataSet(ds))
                        {
                            SendActivationMail(email, ds.Tables[0].Rows[0][0].ToString(), ds.Tables[0].Rows[0][1].ToString(), email);
                            return "Activation mail  is send to your registered mail id";
                        }
                        else
                            return "Your account has already been activated.";
                    }
                    else
                    {
                        return msg != null ? msg.ToString() : "";
                    }

                }
                else if (type == "RP")
                {
                    string query = "declare @hashCode varchar(40)=NEWID() DECLARE @UserId int select @UserId=UserId from UserDetails where EmailAddress='" + email + "'" +
                                    " INSERT INTO ResetPassword values(@hashCode,@UserId) " +
                                    "SELECT PasswordToken FROM ResetPassword WHERE UserId=@UserId";
                    DAO _d = new DAO();
                    object result = _d.ExecuteScalar(query);
                    if (result != null && !string.IsNullOrWhiteSpace(result.ToString()))
                    {
                        SendPasswordResetMail(email, result.ToString());
                        return "Thanks! We have sent you an email with the instructions on how to reset your password.";
                    }
                    else
                    {
                        return "This email is not registered with any account. Please try again. ";
                    }
                }
                return "";
            }
            catch (Exception ex)
            {
                return "Error while performing operation. Try again later";
            }
        }

        internal string GetEmail(string token)
        {
            string query = "Select EmailAddress FROM UserDetails Where UserId in" +
                            " (SELECT UserId from ResetPassword WHERE PasswordToken = '" + token + "') ";
            DAO _d = new DAO();
            object result = _d.ExecuteScalar(query);
            return result != null ? result.ToString() : "";
        }

        internal string ResetPassword(string token, string newPassword)
        {
            object msg = "";
            DAO d = new DAO();
            List<SqlParameter> param = new List<SqlParameter>();
            param.Add(d.AddParameter("@PasswordToken", token));
            param.Add(d.AddParameter("@NewPassword", newPassword));
            param.Add(d.AddParameter("@Message", msg.ToString(), SqlDbType.VarChar, 100, ParameterDirection.Output));
            int ds = d.ExecuteQuery("P_ResetPassword", param, ref msg);
            return msg.ToString() != null ? msg.ToString() : "";
        }

        internal DataSet GetAccountUsers(int roleId)
        {
            object msg = "";
            DAO d = new DAO();
            List<SqlParameter> param = new List<SqlParameter>();
            param.Add(d.AddParameter("@RoleId", roleId));
            DataSet ds = d.ExecuteDataSet("P_GetAccountUsers", param);
            return ds;
        }

        internal string UpdateUserAccount(UserDetails objUser)
        {
            object msg = "";
            DAO d = new DAO();
            List<SqlParameter> param = new List<SqlParameter>();
            param.Add(d.AddParameter("@UserId", objUser.UserId));
            param.Add(d.AddParameter("@UserName", objUser.UserName));
            param.Add(d.AddParameter("@Password", objUser.Password));
            param.Add(d.AddParameter("@Email", objUser.EmailId));
            param.Add(d.AddParameter("@FirstName", objUser.FirstName));
            param.Add(d.AddParameter("@LastName", objUser.LastName));
            param.Add(d.AddParameter("@PhoneNumber", objUser.PhoneNumber));
            param.Add(d.AddParameter("@ExpiryDate", objUser.AccountExpiry));
            param.Add(d.AddParameter("@Company", objUser.Company));
            param.Add(d.AddParameter("@TimeZone", objUser.TimeZone));
            param.Add(d.AddParameter("@DeviceCount", objUser.DeviceLimit));
            param.Add(d.AddParameter("@RoleId", objUser.RoleId));
            param.Add(d.AddParameter("@Message", msg.ToString(), SqlDbType.VarChar, 100, ParameterDirection.Output));
            int ds = d.ExecuteQuery("P_UpdateUsersAccount", param, ref msg);
            return msg.ToString() != null ? msg.ToString() : "";
        }

        internal string DeleteAccount(string userName, string password, int userId, string hashCode)
        {
            object msg = "";
            DAO d = new DAO();
            List<SqlParameter> param = new List<SqlParameter>();
            param.Add(d.AddParameter("@ClientId", userId));
            param.Add(d.AddParameter("@UserName", userName));
            param.Add(d.AddParameter("@Password", password));
            param.Add(d.AddParameter("@HashCode", hashCode));
            param.Add(d.AddParameter("@Message", msg.ToString(), SqlDbType.VarChar, 100, ParameterDirection.Output));
            int ds = d.ExecuteQuery("P_DeactivateAccount", param, ref msg);
            if (msg != null && Convert.ToInt32(msg.ToString()) == 0)
                return "Deleted Successfully";
            else
                return "failed";
        }

        internal string ChangePassword(int userId, string password, string hashCode)
        {
            object msg = "";
            DAO d = new DAO();
            string Query = "Update UserDetails SET Password='" + password + "' WHERE UserId=" + userId.ToString();
            int result = d.ExecuteQuery(Query);
            return result > 0 ? "Success" : "failed";
        }

        internal string RegisterUser(UserDetails objUser)
        {
            object messgae = null;
            DAO d = new DAO();

            List<SqlParameter> param = new List<SqlParameter>();
            param.Add(d.AddParameter("@UserId", objUser.UserId));
            param.Add(d.AddParameter("@HashCode", objUser.HashCode));
            param.Add(d.AddParameter("@EmailId", objUser.EmailId));
            param.Add(d.AddParameter("@UserName", objUser.UserName));
            param.Add(d.AddParameter("@Password", objUser.Password));
            param.Add(d.AddParameter("@FirstName", objUser.FirstName));
            param.Add(d.AddParameter("@LastName", objUser.LastName));
            param.Add(d.AddParameter("@PhoneNumber", objUser.PhoneNumber));
            param.Add(d.AddParameter("@Company", objUser.Company));
            param.Add(d.AddParameter("@DeviceCount", objUser.DeviceLimit));
            if (objUser.ServiceList != null && objUser.ServiceList.Count > 0)
            {
                string serviceListcsv = string.Join(",", objUser.ServiceList.Select(x => x).ToArray());
                param.Add(d.AddParameter("@ServiceList", serviceListcsv));
            }
            if (objUser.ClientId > 0)
            {
                param.Add(d.AddParameter("@ClientId", objUser.ClientId));
            }
            param.Add(d.AddParameter("@Messages", messgae, SqlDbType.VarChar, 500, ParameterDirection.InputOutput));
            try
            {
                d.ExecuteQuery("P_AddUser", param, ref messgae);

                //Send notification to administrator
                SendNotification(objUser);
                // Send activation mail to user
                SendActivationMail(objUser.EmailId, messgae.ToString().Split(',')[0], messgae.ToString().Split(',')[1], objUser.UserName);
                messgae = "0";
            }
            catch
            {

            }
            return messgae.ToString();
        }

        internal DataSet GetDeivces(int clientId, string hashCode)
        {
            object msg = "";
            DAO d = new DAO();
            List<SqlParameter> param = new List<SqlParameter>();
            param.Add(d.AddParameter("@ClientId", clientId));
            param.Add(d.AddParameter("@HashCode", hashCode));
            DataSet ds = d.ExecuteDataSet("P_GetUsersDevice", param);
            return ds;
        }

        internal string ArchiveAndSendMail()
        {
            StringBuilder sbMsg = new StringBuilder();
            DateTime dtRequestDate = DateTime.Now;
            string ccMailIds = "";
            try
            {
                DAO _d = null;
                _d = new DAO();
                //getting distinct clients & their Email Address
                DataSet ds = _d.ExecuteDataSet("SELECT DISTINCT UserId,EmailAddress From UserDetails Where ISNULL(ClientId,0)=0");

                if (Utility.IsValidDataSet(ds))
                {
                    for (int i = 0; i < ds.Tables[0].Rows.Count; i++)
                    {
                        _d = new DAO();
                        List<SqlParameter> lstParam = new List<SqlParameter>();
                        lstParam.Add(_d.AddParameter("@ClientId", ds.Tables[0].Rows[i][0]));
                        DataSet dsDetail = _d.ExecuteDataSet("ArchiveScanData", lstParam);
                        if (Utility.IsValidDataSet(dsDetail))
                        {
                            ccMailIds = GetEmailIds(ds.Tables[0].Rows[i][0].ToString());
                            sbMsg.Append(SendDBClearMail(dsDetail, ds.Tables[0].Rows[i][1].ToString(), ccMailIds, dtRequestDate));
                        }
                    }
                    return sbMsg.ToString();
                }
                return "No data found for archival";
            }
            catch (Exception ex)
            {

                throw new Exception(ex.Message);
            }
        }
        private string SendDBClearMail(DataSet dsData, string emailId, string ccMailId, DateTime requestDate)
        {
            StringBuilder sbMessage = new StringBuilder();
            Decimal tmpNum = 0;
            Decimal sum = 0;
            try
            {
                List<Scans> _lstScan = new List<Scans>();
                for (int i = 0; i < dsData.Tables[0].Rows.Count; i++)
                {
                    _lstScan.Add(new Scans()
                    {
                        ScanId = Utility.GetInt(dsData.Tables[0].Rows[i]["ScanId"]),
                        StudentIdNumber = Utility.GetInt(dsData.Tables[0].Rows[i]["StudentIdNumber"]),
                        FirstName = Utility.GetString(dsData.Tables[0].Rows[i]["FirstName"]),
                        LastName = Utility.GetString(dsData.Tables[0].Rows[i]["LastName"]),
                        DeviceName = Utility.GetString(dsData.Tables[0].Rows[i]["DeviceName"]),
                        UserName = Utility.GetString(dsData.Tables[0].Rows[i]["UserName"]),
                        ServiceName = Utility.GetString(dsData.Tables[0].Rows[i]["ServiceName"]),
                        ScanStatusMessage = Utility.GetString(dsData.Tables[0].Rows[i]["Message"]),
                        Grade = Utility.GetInt(dsData.Tables[0].Rows[i]["Grade"]),
                        ValidityCode = Utility.GetInt(dsData.Tables[0].Rows[i]["Validity"]),
                        Note1 = Utility.GetString(dsData.Tables[0].Rows[i]["Note1"]),
                        Note2 = Utility.GetString(dsData.Tables[0].Rows[i]["Note2"]),
                        Note3 = Utility.GetString(dsData.Tables[0].Rows[i]["Note3"]),
                        Note4 = Utility.GetString(dsData.Tables[0].Rows[i]["Note4"]),
                        CreateDate = Utility.GetString(dsData.Tables[0].Rows[i]["CreatedDate"])
                    });
                }
                if (_lstScan != null)
                {
                    StringBuilder sb = new StringBuilder();
                    MemoryStream stream = new MemoryStream();
                    using (StreamWriter str = new StreamWriter(stream))
                    {
                        sb.Append("Scan ID");
                        sb.Append(",");
                        sb.Append("Device Name");
                        sb.Append(",");
                        sb.Append("Service Name");
                        sb.Append(",");
                        sb.Append("User name");
                        sb.Append(",");
                        sb.Append("Student#");
                        sb.Append(",");
                        sb.Append("First Name");
                        sb.Append(",");
                        sb.Append("Last Name");
                        sb.Append(",");
                        sb.Append("Grade");
                        sb.Append(",");
                        sb.Append("Validity");
                        sb.Append(",");
                        sb.Append("Note1");
                        sb.Append(",");
                        sb.Append("Note2");
                        sb.Append(",");
                        sb.Append("Note3");
                        sb.Append(",");
                        sb.Append("Note4");
                        sb.Append(",");
                        sb.Append("Timestamp Scanned");
                        sb.Append(",");
                        sb.Append("Timestamp Received");
                        sb.Append(",");
                        sb.Append("Scan Type");
                        str.WriteLine(sb.ToString());
                        for (int i = 0; i < _lstScan.Count; i++)
                        {

                            sb = new StringBuilder();
                            sb.Append(_lstScan[i].ScanId.ToString());
                            sb.Append(",");
                            sb.Append(_lstScan[i].DeviceName.ToString());
                            sb.Append(",");
                            sb.Append(_lstScan[i].ServiceName.ToString());
                            sb.Append(",");
                            sb.Append(_lstScan[i].UserName.ToString());
                            sb.Append(",");
                            sb.Append(_lstScan[i].StudentIdNumber.ToString());
                            sb.Append(",");
                            sb.Append(_lstScan[i].FirstName.ToString());
                            sb.Append(",");
                            sb.Append(_lstScan[i].LastName.ToString());
                            sb.Append(",");
                            sb.Append(_lstScan[i].Grade.ToString());
                            sb.Append(",");
                            sb.Append(_lstScan[i].ValidityCode.ToString());
                            sb.Append(",");
                            sb.Append(_lstScan[i].Note1.ToString());
                            sb.Append(",");
                            sb.Append(_lstScan[i].Note2.ToString());
                            sb.Append(",");
                            sb.Append(_lstScan[i].Note3.ToString());
                            sb.Append(",");
                            sb.Append(_lstScan[i].Note4.ToString());
                            sb.Append(",");
                            sb.Append(_lstScan[i].CreateDate.ToString());
                            sb.Append(",");
                            sb.Append(_lstScan[i].CreateDate.ToString());
                            sb.Append(",");
                            sb.Append(_lstScan[i].ScanStatusMessage.Trim().ToLower().Contains("duplicate") ? "Duplicate" : " ");
                            str.WriteLine(sb.ToString());
                            if (Decimal.TryParse(_lstScan[i].Note1, out tmpNum))
                                sum = sum + tmpNum;
                        }
                        if (sum > 0)
                        {
                            sb.Append(" ");
                            sb.Append(",");
                            sb.Append(" ");
                            sb.Append(",");
                            sb.Append(" ");
                            sb.Append(",");
                            sb.Append(" ");
                            sb.Append(",");
                            sb.Append(" ");
                            sb.Append(",");
                            sb.Append(" ");
                            sb.Append(",");
                            sb.Append(" ");
                            sb.Append(",");
                            sb.Append(" ");
                            sb.Append(",");
                            sb.Append("Total");
                            sb.Append(",");
                            sb.Append(" $" + sum.ToString());
                            sb.Append(",");
                            sb.Append(" ");
                            sb.Append(",");
                            sb.Append(" ");
                            sb.Append(",");
                            sb.Append(" ");
                            sb.Append(",");
                            sb.Append(" ");
                            sb.Append(",");
                            sb.Append(" ");
                            sb.Append(",");
                            sb.Append(" ");
                            str.WriteLine(sb.ToString());
                        }
                        str.Flush();
                        stream.Seek(0, SeekOrigin.Begin);

                        if (!string.IsNullOrWhiteSpace(emailId))
                        {
                            Emailer _mail1 = new Emailer();
                            _mail1.To = emailId;
                            stream.Seek(0, SeekOrigin.Begin);
                            _mail1.File = new System.Net.Mail.Attachment(stream, "ArchivedScans.csv", "application/vnd.xls");
                            if (!string.IsNullOrWhiteSpace(ccMailId))
                            {
                                _mail1.CC = ccMailId;
                            }
                            _mail1.Subject = "Scan data for the day " + DateTime.Now;
                            _mail1.MailBody = @"Please find the attached file for the scans of the day " + requestDate + "<br/><br/><br/>" +
                                               "This is an automated message. Please do not reply to this email. If you need help, please contact us at support@ssica.org.<br/><br/><br/><br/>" +
                                               "Thanks,<br/>" +
                                               "The SSICA Team";
                            _mail1.SendMail();
                            sbMessage.Append("Mail Sent with data of size <b>" + stream.Length.ToString() + "</b> bytes to client <b>" + emailId.ToString() + "</b><br/>");
                        }

                    }

                }
                return sbMessage.ToString();
            }
            catch (Exception ex)
            {
                return ex.Message;
            }
        }

        internal string AddComment(int commentId, int CommentUserId, string CommentDesc, int loggedinUser)
        {
            string msg = "";
            try
            {
                string query = "";
                if (commentId > 0)
                {
                    query = "UPDATE AdminComments SET CommentDesc='" + CommentDesc + "',UpdatedBy=" + loggedinUser.ToString() + ",UpdateDate=GETUTCDATE() WHERE CommentId=" + commentId.ToString();
                    msg = query;
                }
                else
                {
                    query = "INSERT INTO AdminComments(CommentDesc,CommentOnUser,CommentBy,CommentDate) VALUES('" + CommentDesc + "'," + CommentUserId.ToString() + "," + loggedinUser + ",GETUTCDATE())";
                    msg = "Comment Added Successfully";
                }
                DAO _d = new DAO();
                _d.ExecuteQuery(query);
                return msg;
            }
            catch (Exception ex)
            {
                return "Error while adding comment. error is : " + ex.Message;
            }
        }

        internal DataSet GetComments(int commentedUserId)
        {
            try
            {
                string q = @"select CommentId,CommentDesc,CommentOnUser,UD.AppUserName,CommentDate
                            from AdminComments AC
                            left join UserDetails UD on UD.UserId = AC.CommentBy ";
                string query = q + " where CommentOnUser=" + commentedUserId.ToString();
                DAO _d = new DAO();
                return _d.ExecuteDataSet(query);
            }
            catch (Exception)
            {
                return null;
            }
        }
        internal string DeleteComment(int commentId)
        {
            string query = "";
            query = "DELETE FROM AdminComments WHERE CommentId=" + commentId.ToString();
            DAO _d = new DAO();
            if (_d.ExecuteQuery(query) > 0)
                return "Comment Deleted Successfully";
            else
                return "";
        }

        internal string GetEmailIds(string userId)
        {
            string query = "";
            query = "SELECT ISNULL(OtherEmailIds,'') As Email FROM UserDetails WHERE UserId=" + userId;
            DAO _d = new DAO();
            return _d.ExecuteString(query);
        }
        internal string AddEmailIds(string userId, string hashCode, string emailIds, string loggedinUser)
        {
            try
            {
                if (!string.IsNullOrWhiteSpace(emailIds))
                    emailIds = emailIds.Replace(';', ',');

                string query = "";
                query = "UPDATE UserDetails SET OtherEmailIds='" + emailIds + "',UpdatedBy=" + loggedinUser + ",UpdateDate=GETUTCDATE() WHERE UserId=" + userId;
                DAO _d = new DAO();
                if (_d.ExecuteQuery(query) > 0)
                    return "0";
                else
                    return "Error while adding Email Id(s)";
            }
            catch (Exception ex)
            {
                return "Error while adding Email Id(s). error is : " + ex.Message;
            }
        }
    }
}